package ntd.util.frame.data.tool;

import cn.hutool.core.collection.CollStreamUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.text.StrBuilder;
import cn.hutool.core.text.StrFormatter;
import cn.hutool.core.util.*;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.sql.SqlBuilder;
import cn.hutool.db.sql.SqlUtil;
import cn.hutool.log.StaticLog;
import ntd.util.UtilCatch;
import ntd.util.config.common.enums.EnumDataRule;
import ntd.util.config.common.enums.EnumDataType;
import ntd.util.config.common.exception.ComException;
import ntd.util.frame.data.bo.MetaTable;
import ntd.util.frame.data.bo.MetaTableField;
import ntd.util.util.ToolDuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

public class ToolMysql {

    private static final String[] EXCLUDE_TABLE_NAMES = {"aaa_template", "magic_api_file", "magic_api_backup"};

    public Db db;
    public ToolMysql(DataSource dataSource) {
        this.db = Db.use(dataSource);
    }

    /**
     * 获取元数据
     * @param tableName
     * @return
     */
    public List<MetaTableField> tableInfo(String tableName) {
        return UtilCatch.tryCatch(() -> {
            String currentDatabase = db.queryString("select database()");

            String sql = StrFormatter.format("select * from information_schema.COLUMNS where TABLE_NAME='{}' and table_schema = '{}' ORDER BY ORDINAL_POSITION",
                    tableName, currentDatabase);

            List<Entity> entities = db.query(sql);

            return CollStreamUtil.toList(entities, MetaTableField::transform);
        });
    }

    /**
     * 获取表列表
     * @param tableName
     * @return
     * @throws SQLException
     */
    public List<MetaTable> table(String tableName) throws SQLException {
        StrBuilder strBuilder = StrBuilder.create("SELECT " +
                "table_name NAME," +
                "table_comment memo," +
                "table_schema `SCHEMA`," +
                "ENGINE," +
                "version," +
                "table_rows tableRows," +
                "data_length dataLength," +
                "create_time createTime " +
                "FROM " +
                "information_schema.TABLES " +
                "WHERE " +
                "1 = 1");

        String currentDatabase = db.queryString("select database()");

        strBuilder.append(StrFormatter.format(" and table_schema = '{}'", currentDatabase));

        if (ObjectUtil.isNotEmpty(tableName)) {
            strBuilder.append(StrFormatter.format(" and table_name = '{}'", tableName));
        }

        List<MetaTable> metaTables = db.query(strBuilder.toString(), MetaTable.class);

        CollectionUtil.filter(metaTables, metaTable -> !ArrayUtil.contains(EXCLUDE_TABLE_NAMES, metaTable.getName()));

        return metaTables;
    }

    /**
     * mysql - 类型转换 -
     * @param type
     * @return
     */
    public static EnumDataType transformType(String type) {
        String group0 = ReUtil.getGroup0("\\w*", type);
        return EnumDataType.transform(group0);
    }

    public static int transformLength(String type) {
        String group1 = ReUtil.getGroup1("\\(([0-9]*)\\)", type);
        return Convert.toInt(group1, 0);
    }

    /**
     * 根据主键查询数据
     * @param tableName
     * @param id
     * @return
     */
    public Entity findById(String tableName, String id) {
        return findOne(tableName, "id", id);
    }

    /**
     * 单个对象查询
     *
     * @param tableName
     * @param field
     * @param val
     * @return
     */
    public Entity findOne(String tableName, String field, String val) {
        try {
            List<Entity> entities = db.findBy(tableName, field, val);
            if (entities.size() < 1) {
                return null;
            }
            if (entities.size() > 1) {
                throw new ComException("数据[{}]({})查询异常, 超出预期数量", tableName, val);
            }
            return entities.get(0);
        } catch (SQLException e) {
            StaticLog.error(e, "[ToolMqsql][findOne][{}]失败...", tableName);
        }
        return null;
    }

    /**
     * 包装最大最小值查询方案
     *
     * @param where
     * @return
     */
    public List<Entity> comFind(Entity where) {
        String baseSql = StrUtil.format("select t.* from {} t", where.getTableName());
        String whereSql = " where 1 = 1";

        Map<String, Object> params = new HashMap<>();

        Set<String> fieldNames = where.keySet();

        Set<String> maxOrMin = CollectionUtil.filter(CollectionUtil.newHashSet(fieldNames), s -> EnumDataRule.maxOrMin().contains(EnumDataRule.val(s)));
        Set<String> fields = CollectionUtil.filter(fieldNames, s -> !EnumDataRule.maxOrMin().contains(EnumDataRule.val(s)));

        for (String field : fields) {
            Object obj = where.getObj(field);
            if (ObjectUtil.isNotNull(obj)) {
                // 普通值处理方案
                if (ClassUtil.isBasicType(obj.getClass()) || CharSequence.class.isAssignableFrom(obj.getClass())) {
                    whereSql += StrUtil.format(" and t.{} = @{}", field, field);
                    params.put(field, obj);
                }
                // 数组处理方案
                if (ArrayUtil.isArray(obj)) {
                    if (ArrayUtil.isEmpty(obj)) {
                        throw new ComException("数据库查询in操作，值不能为空！");
                    }
                    whereSql += StrUtil.format(" and t.{} in (@{})", field, field);
                    params.put(field, ArrayUtil.join(obj, ","));
                }
                // 集合处理方案
                if (Collection.class.isAssignableFrom(obj.getClass())) {
                    List<?> list = Convert.toList(obj);
                    if (CollectionUtil.isEmpty(list)) {
                        throw new ComException("数据库查询in操作，值不能为空！");
                    }
                    whereSql += StrUtil.format(" and t.{} in (@{})", field, field);
                    String join = CollectionUtil.join(list, ",");
                    params.put(field, join);
                }
            }
        }

        // 最大值、最小值 包装 20230118
        for (String fieldName : maxOrMin) {
            String col = where.getStr(fieldName);
            if (EnumDataRule.RULE_MAX.name().equals(fieldName)) {
                String onSql = StrUtil.format(" t.id = t2.id and t.{} < t2.{} and t2.{} is null", col, col, col);
                baseSql += StrUtil.format(" left join t2 on t.id = t2.id and t.{} < t2.{}", col, col);
                whereSql += StrUtil.format(" and t2.{} is null", col);
            }
            if (EnumDataRule.RULE_MIN.name().equals(fieldName)) {
                baseSql += StrUtil.format(" left join t2 on t.id = t2.id and t.{} > t2.{}", col, col);
                whereSql += StrUtil.format(" and t2.{} is null", col);
            }
        }

        String finalSql = baseSql + whereSql;
        return ToolDuration.duration(() -> db.query(finalSql, params), StrUtil.format("执行sql[{}]", finalSql));
    }


    public List<Entity> comQuery(Entity where) {
        Set<Map.Entry<String, Object>> fieldsEntity = where.entrySet();

        // 取出最大最小值属性
        HashSet<Map.Entry<String, Object>> maxOrMin = CollectionUtil.filter(CollectionUtil.newHashSet(fieldsEntity),
                s -> EnumDataRule.maxOrMin().contains(EnumDataRule.val(s.getKey())));
        // 过滤最大最小值属性
        CollectionUtil.filter(fieldsEntity, s -> !EnumDataRule.maxOrMin().contains(EnumDataRule.val(s.getKey())));

        Entity newEntity = new Entity(where.getTableName() + " t");
        newEntity.setFieldNames("t.*");
        where.entrySet().forEach(stringObjectEntry -> newEntity.set("t." + stringObjectEntry.getKey(), stringObjectEntry.getValue()));

        // 最大值、最小值 包装 20230118
        for (Map.Entry<String, Object> entry : maxOrMin) {
            String col = entry.getValue().toString();
            if (EnumDataRule.RULE_MAX.name().equals(entry.getKey())) {

                newEntity.set(col, getMaxOrMinVal(newEntity.clone(), col, "max"));
//                String onSql = StrUtil.format(" t.id = t2.id and t.{} < t2.{} and t2.{} is null", col, col, col);
//                sqlBuilder.join(where.getTableName() + " t2", SqlBuilder.Join.LEFT)
//                        .on(onSql);
            }
            if (EnumDataRule.RULE_MIN.name().equals(entry.getKey())) {
                newEntity.set(col, getMaxOrMinVal(newEntity.clone(), col, "min"));
//                String onSql = StrUtil.format(" t.id = t2.id and t.{} > t2.{} and t2.{} is null", col, col, col);
//                sqlBuilder.join(where.getTableName() + " t2", SqlBuilder.Join.LEFT)
//                        .on(onSql);
            }
        }
        SqlBuilder sqlBuilder = getSqlBuilder(newEntity);

        StaticLog.info("======== 临时[{}][{}] =========", sqlBuilder.build(), sqlBuilder.getParamValueArray());

        return ToolDuration.duration(() -> db.query(sqlBuilder.build(), sqlBuilder.getParamValueArray()), StrUtil.format("执行查询"));
    }

    private static SqlBuilder getSqlBuilder(Entity clone) {
        return SqlBuilder.create().select(clone.getFieldNames()).from(clone.getTableName()).where(SqlUtil.buildConditions(clone));
    }

    private String getMaxOrMinVal(Entity entity, String col, String rule) {
        SqlBuilder sqlBuilder = getSqlBuilder(entity.setFieldNames(StrUtil.format("{}(t.{}) as val", rule, col)));

        StaticLog.info("======== maxOrMin值查询[{}][{}] =========", sqlBuilder.build(), sqlBuilder.getParamValueArray());

        List<Entity> entities = UtilCatch.tryCatch(() -> db.query(sqlBuilder.build(), sqlBuilder.getParamValueArray()));
        if (entities.size() < 1) {
            throw new ComException("最大值规则读取失败");
        }
        return entities.get(0).getStr("val");
    }

    public static String listToSqlStr(List<String> list) {
        StrBuilder strBuilder = StrBuilder.create();
        strBuilder.append("(");
        for (int i = 0; i < list.size(); i++) {
            strBuilder.append(StrUtil.format("'{}'", list.get(i)));
            if (i != list.size() - 1) {
                strBuilder.append(",");
            }
        }
        strBuilder.append(")");
        return strBuilder.toString();
    }
}
